﻿using DataAchive.Common;
using DataAchive.Model.Excel.Export;
using MySql.Data.MySqlClient;
using NPOI.SS.Formula.Functions;
using SoftCommon;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlTypes;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using static NPOI.HSSF.Util.HSSFColor;

namespace DataAchive.Helper
{
    public class ReportHelper
    {

        public static string GetWhereCause(WhereQ whereCause)
        {
            string whereStr=string.Empty;
            StringBuilder sb = new StringBuilder();
            //if(whereCause.Date ==null||whereCause.Date==DateTime.MinValue)
            //{
            //    whereStr= $" WHERE date >= '{DateTime.Now.ToString("yyyy-MM-dd")}' AND date <= '{DateTime.Now.AddDays(1).ToString("yyyy-MM-dd")}'";
            //}
            //else
            //{
            //    whereStr = $" WHERE date >= '{whereCause.Date.ToString("yyyy-MM-dd")}' AND date <= '{DateTime.Now.AddDays(1).ToString("yyyy-MM-dd")}'";
            //}

           
            //else
            //{
            //    whereStr = $" WHERE date >= '{whereCause.Date.ToString("yyyy-MM-dd")}' AND date <= '{DateTime.Now.AddDays(1).ToString("yyyy-MM-dd")}'";
            //}

            whereStr = $" WHERE date >= '{whereCause.BeginTime.ToString("yyyy-MM-dd HH:mm:ss")}'";
            whereStr= whereStr+ $" AND date <= '{whereCause.EndTime.ToString("yyyy-MM-dd HH:mm:ss")}'";

            if (!string.IsNullOrWhiteSpace(whereCause.EquitepmentId))
            {
                whereStr += $" AND equipment_id='{whereCause.EquitepmentId}' ";
            }
            if(whereCause.ManyEquitepmentIds!=null && whereCause.ManyEquitepmentIds.Count > 0)
            {
                List<string>eids = new List<string>();
                whereCause.ManyEquitepmentIds.ForEach(m => eids.Add($"'{m}'") );
                  var equipList = string.Join(",", eids);
                whereStr += $" AND equipment_id in ({equipList}) ";
            }

            SoftCommon.Log.Write("where:"+whereStr);
            return whereStr ;
        }
        public static DataSet GetDetailReportInfo(WhereQ whereq, string connstr,string exSql=null)
        {           
            string wherestr = GetWhereCause(whereq);
            string cmdText=string.Empty;
            if (string.IsNullOrWhiteSpace(exSql))
            {
                 cmdText = DetailSql + wherestr;
            }
            else
            {
                 cmdText = exSql + wherestr;
            }
        
            using (MySqlConnection connection = new MySqlConnection(connstr))
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    if (connection.State != ConnectionState.Open)
                        connection.Open();
                    cmd.Connection = connection;
                    cmd.CommandText = cmdText;

                    cmd.Connection = connection;

                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        try
                        {
                            da.Fill(ds, "ds");
                            cmd.Parameters.Clear();
                        }
                        catch (Exception ex)
                        {
                            SoftCommon.Log.Write($"SQL异常：{ex.Message}");
                            throw ex;
                        }
                        return ds;
                    }
                }
                catch (Exception e)
                {
                    SoftCommon.Log.Write("GetDetailReportInfo连接异常：" + e.Message);
                    MessageBox.Show("连接异常");
                    throw;
                }
               
            }

        }


        //
        public static List<Dictionary<string, object>> DetailReportTrans(DataTable dataTable)
        {
            if (dataTable == null||dataTable.Rows.Count==0)
                return null;
            List<Dictionary<string, object>>listPairs = new List<Dictionary<string, object>>();
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                try
                {
                    // 创建一个字典来存储当前行的键值对
                    Dictionary<string, object> rowDict = new Dictionary<string, object>();

                    // 遍历表头列名
                    foreach (DataColumn column in dataTable.Columns)
                    {
                        string columnName = column.ColumnName;

                        // 判断单元格是否为空
                        if (dataTable.Rows[i][columnName] != DBNull.Value)
                        {
                            rowDict[columnName] = dataTable.Rows[i][columnName];
                        }
                        else
                        {
                            rowDict[columnName] = null; // 或者设置默认值
                        }
                    }

                
                    listPairs.Add(rowDict);
                }
                catch (Exception e)
                {

                    SoftCommon.Log.Write(e.Message);
                    continue;
                }
              
            }
            return listPairs;

        }

        public static List<DetailReport> DicToList(List<Dictionary<string, object>> listPairs)
        {
            if (listPairs == null || listPairs.Count == 0)
            {
                return new List<DetailReport>();
            }
            List<DetailReport> reports = new List<DetailReport>();
            foreach (var dics in listPairs)
            {
                DetailReport report1 = new DetailReport();
                foreach (KeyValuePair<string, object> item in dics)
                {
                    try
                    {
                        if (item.Key.Equals("lotid", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.LotId = "";
                            }
                            else
                            {
                                report1.LotId = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("date", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.CreateTime = DateTime.MinValue;
                            }
                            else
                            {
                                report1.CreateTime = Convert.ToDateTime(item.Value);
                            }
                        }
                        else if (item.Key.Equals("checked", StringComparison.OrdinalIgnoreCase))
                        {
                         
                        }
                        //0 el 1vi 2 back
                        else if (item.Key.Equals("type", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.DetectType = "";
                            }
                            else
                            {
                                report1.DetectType = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("id", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.Id = -1;
                            }
                            else
                            {
                                report1.Id = Convert.ToInt32(item.Value);
                            }
                        }
                        else if (item.Key.Equals("site_id", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.SiteId = -1;
                            }
                            else
                            {
                                report1.SiteId = Convert.ToInt32(item.Value);
                            }
                        }
                        else if (item.Key.Equals("finishtime", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.FinishTime = DateTime.MinValue;
                            }
                            else
                            {
                                report1.FinishTime = Convert.ToDateTime(item.Value);
                            }
                        }
                        else if (item.Key.Equals("airesult", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.AiDetectResult = "";
                            }
                            else
                            {
                                report1.AiDetectResult = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("manualresult", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.HumanDetectResult = "";
                            }
                            else
                            {
                                report1.HumanDetectResult = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("mes_check", StringComparison.OrdinalIgnoreCase))
                        {

                        }
                        else if (item.Key.Equals("line_id", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.LineId = "";
                            }
                            else
                            {
                                report1.LineId = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("equipment_id", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.Equipment = "";
                            }
                            else
                            {
                                report1.Equipment = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("classify", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.Classify = "";
                            }
                            else
                            {
                                report1.Classify = item.Value.ToString();
                            }
                        }

                     
                    }
                    catch (Exception ee)
                    {

                        SoftCommon.Log.Write("transfer err:"+ee.Message);
                        continue;
                    }
                   
                
                }
                
                reports.Add(report1);
            }

            return reports;

        }

        public static List<AllNGInfoRepChange> AllNginfoToList(DataSet ds)
        {
            List<AllNGInfoReport> list = new M_QueryBuilder().DataSetToList<AllNGInfoReport>(ds);
          
            List<AllNGInfoRepChange>repChanges = new List<AllNGInfoRepChange>();
            foreach (var item in list)
            {
                AllNGInfoRepChange allNGInfo = new AllNGInfoRepChange()
                {
                    AI_Info = item.AI_Info,
                    AI_Result = item.AI_Result,
                    Cell_Location = item.Cell_Location,
                    Checked = item.Checked,
                    Classify = item.Classify,
                    Colum_Number = item.Colum_Number,
                    Colum_Order = item.Colum_Order,
                    Colum_Type = item.Colum_Type,
                    Creator = item.Creator,
                    Cuttent_Shift = item.Cuttent_Shift,
                    Date = item.Date.GetValueOrDefault().ToString("yyyy-MM-dd HH:mm:ss"),
                    Equipment_Id = item.Equipment_Id,
                    Finish_Time = item.Finish_Time.GetValueOrDefault().ToString("yyyy-MM-dd HH:mm:ss"),
                    Height = item.Height,
                    ID = item.ID,
                    Info = item.Info,
                    IP = item.IP,
                    Left_Right_Margin = item.Left_Right_Margin,
                    Line_Id = item.Line_Id,
                    Lot_Id = item.Lot_Id,
                    Manua_Result = item.Manua_Result,
                    Mes_Check = item.Mes_Check,
                    PX = item.PX,
                    PY = item.PY,
                    Row_Number = item.Row_Number,
                    Row_Order = item.Row_Order,
                    Row_Type = item.Row_Type,
                    Shiftings = item.Shiftings,
                    Shift_Start_Time = item.Shift_Start_Time,
                    Site_Id = item.Site_Id,
                    Top_Bottom_Margin = item.Top_Bottom_Margin,                    
                    Width = item.Width,
                };
                if (item.Type==null)
                {
                    allNGInfo.Type = "";
                }
                else if (item.Type == 0)
                {
                    allNGInfo.Type = "EL";
                }
                else if (item.Type == 1)
                {
                    allNGInfo.Type = "正面";
                }
                else if (item.Type == 2)
                {
                    allNGInfo.Type = "背面";
                }
                else
                {
                    allNGInfo.Type = "";
                }

                repChanges.Add(allNGInfo);
            }

            return repChanges;

        }

        //igm
        public const  string DetailSql = " SELECT `lotid`,  `info`,    `checked`,  `date`,  `type`,  `aiinfo`,  ng.`id` AS id,  `site_id`,  `finishtime`,  `airesult`, " +
            " `manualresult`,  `mes_check`,  `line_id`,  `equipment_id`,  `ip`,  `row_number`,  `row_type`,  `row_order`,  `column_number`,  `column_type`,  `column_order`,  `top_bottom_margin`,  `left_right_margin`,  `shift_start_time`,  `shiftings`,  `currentshift`,   `classify`,  `px`, " +
            " `py`,  `width`,  `height`,  `ngId`,  `creator`,  `celllocation` FROM ng  LEFT JOIN site ON ng.`site_id`=site.id LEFT JOIN info ON ng.`id`=info.`ngId`";

        public const string DetailLittleSql = " SELECT `lotid`, `date`,  `type`,  `aiinfo`,  ng.`id` AS id,  `site_id`,  `finishtime`,  `airesult`, " +
           " `manualresult`,  `mes_check`,   `equipment_id` " +
           "  FROM ng  LEFT JOIN site ON ng.`site_id`=site.id ";


        public static DataSet GetColRowReportInfo(WhereQ whereq, string connstr, bool show = true)
        {           
            string wherestr = GetWhereCause(whereq);
            string cmdText = ColRowSql1 + wherestr+ ColRowSql2;
            using (MySqlConnection connection = new MySqlConnection(connstr))
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    if (connection.State != ConnectionState.Open)
                        connection.Open();
                    cmd.Connection = connection;
                    cmd.CommandText = cmdText;

                    cmd.Connection = connection;

                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        try
                        {
                            da.Fill(ds, "ds");
                            cmd.Parameters.Clear();
                        }
                        catch (Exception ex)
                        {
                            SoftCommon.Log.Write($"SQL异常：{ex.Message}");
                            throw ex;
                        }
                        return ds;
                    }
                }
                catch (Exception e)
                {
                    SoftCommon.Log.Write("GetDetailReportInfo连接异常：" + e.Message);
                    if (show)
                    {
                        MessageBox.Show("连接异常");
                    }                   
                    throw;
                }

            }

        }

        public static List<ColRowReport> DicColRowToList(List<Dictionary<string, object>> listPairs)
        {
            if (listPairs == null || listPairs.Count == 0)
            {
                return new List<ColRowReport>();
            }
            List<ColRowReport> reports = new List<ColRowReport>();
            foreach (var dics in listPairs)
            {
                ColRowReport report1 = new ColRowReport();
                foreach (KeyValuePair<string, object> item in dics)
                {
                    try
                    {
                        if (item.Key.Equals("lotid", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.LotId = "";
                            }
                            else
                            {
                                report1.LotId = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("ELJilu", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ElCreateTime = DateTime.MinValue;
                            }
                            else
                            {
                                report1.ElCreateTime = Convert.ToDateTime(item.Value);
                            }
                        }
                        else if (item.Key.Equals("VIJilu", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.VICreateTime = DateTime.MinValue;
                            }
                            else
                            {
                                report1.VICreateTime = Convert.ToDateTime(item.Value);
                            }
                        }

                        else if (item.Key.Equals("VBJilu", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.VBCreateTime = DateTime.MinValue;
                            }
                            else
                            {
                                report1.VBCreateTime = Convert.ToDateTime(item.Value);
                            }
                        }

                        else if (item.Key.Equals("checked", StringComparison.OrdinalIgnoreCase))
                        {

                        }
        
                        else if (item.Key.Equals("id", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.Id = -1;
                            }
                            else
                            {
                                report1.Id = Convert.ToInt32(item.Value);
                            }
                        }
                        else if (item.Key.Equals("site_id", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.SiteId = -1;
                            }
                            else
                            {
                                report1.SiteId = Convert.ToInt32(item.Value);
                            }
                        }
                        else if (item.Key.Equals("ELwancheng", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ElFinishTime = DateTime.MinValue;
                            }
                            else
                            {
                                report1.ElFinishTime = Convert.ToDateTime(item.Value);
                            }
                        }
                        else if (item.Key.Equals("VIwancheng", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.VIFinishTime = DateTime.MinValue;
                            }
                            else
                            {
                                report1.VIFinishTime = Convert.ToDateTime(item.Value);
                            }
                        }
                        else if (item.Key.Equals("VBwancheng", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.VBFinishTime = DateTime.MinValue;
                            }
                            else
                            {
                                report1.VBFinishTime = Convert.ToDateTime(item.Value);
                            }
                        }
                        else if (item.Key.Equals("ELairesult", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ElAiDetectResult = "";
                            }
                            else
                            {
                                report1.ElAiDetectResult = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("VIairesult", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.VIAiDetectResult = "";
                            }
                            else
                            {
                                report1.VIAiDetectResult = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("VBairesult", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.VBAiDetectResult = "";
                            }
                            else
                            {
                                report1.VBAiDetectResult = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("ELRengongResult", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ElHumanDetectResult = "";
                            }
                            else
                            {
                                report1.ElHumanDetectResult = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("VIRengongResult", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.VIHumanDetectResult = "";
                            }
                            else
                            {
                                report1.VIHumanDetectResult = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("VBRengongResult", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.VBHumanDetectResult = "";
                            }
                            else
                            {
                                report1.VBHumanDetectResult = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("mes_check", StringComparison.OrdinalIgnoreCase))
                        {

                        }
                        else if (item.Key.Equals("line_id", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.LineId = "";
                            }
                            else
                            {
                                report1.LineId = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("equipment_id", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.Equipment = "";
                            }
                            else
                            {
                                report1.Equipment = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("classify", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.Classify = "";
                            }
                            else
                            {
                                report1.Classify = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("ELAiQX", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ElAiDetectInfo = "";
                            }
                            else
                            {
                                report1.ElAiDetectInfo = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("VIAiQX", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ViAiDetectInfo = "";
                            }
                            else
                            {
                                report1.ViAiDetectInfo = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("VBAiQX", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.VBAiDetectInfo = "";
                            }
                            else
                            {
                                report1.VBAiDetectInfo = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("ELrengongQX", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ElHumanDetectInfo = "";
                            }
                            else
                            {
                                report1.ElHumanDetectInfo = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("VIrengongQX", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.VIHumanDetectInfo = "";
                            }
                            else
                            {
                                report1.VIHumanDetectInfo = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("VBrengongQX", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.VBHumanDetectInfo = "";
                            }
                            else
                            {
                                report1.VBHumanDetectInfo = item.Value.ToString();
                            }
                        }


                    }
                    catch (Exception ee)
                    {

                        SoftCommon.Log.Write("transfer err:" + ee.Message);
                        continue;
                    }


                }

                reports.Add(report1);
            }

            return reports;

        }

        //igm
        public const string ColRowSql1 = " SELECT `lotid` ,    MAX(`checked`) AS checked,        MAX(  CASE WHEN `type`=0 THEN `date` ELSE NULL END) AS 'ELJilu' ,        MAX(  CASE WHEN `type`=1 THEN `date` ELSE NULL END) AS 'VIJilu' ,       MAX( CASE WHEN `type`=2 THEN `date` ELSE NULL END) AS 'VBJilu' ,        MAX(  CASE WHEN `type`=0 THEN `info` ELSE NULL END )AS 'ELrengongQX' ,      MAX( CASE WHEN `type`=1 THEN `info` ELSE NULL END) AS 'VIrengongQX' ,     MAX( CASE WHEN `type`=2 THEN `info` ELSE NULL END) AS 'VBrengongQX' ,          MAX(  CASE WHEN `type`=0 THEN `aiinfo` ELSE NULL END) AS 'ELAiQX' ,     MAX( CASE WHEN `type`=1 THEN `aiinfo` ELSE NULL END) AS 'VIAiQX' ,  MAX( CASE WHEN `type`=2 THEN `aiinfo` ELSE NULL END) AS 'VBAiQX' ,       MAX(`site_id`)AS site_id,                   MAX(  CASE WHEN `type`=0 THEN `finishtime` ELSE NULL END) AS 'ELwancheng' ,      MAX( CASE WHEN `type`=1 THEN `finishtime` ELSE NULL END) AS 'VIwancheng' ,        MAX( CASE WHEN `type`=2 THEN `finishtime` ELSE NULL END) AS 'VBwancheng' ,          MAX( CASE WHEN `type`=0 THEN `airesult` ELSE NULL END) AS 'ELairesult' ,       MAX(  CASE WHEN `type`=1 THEN `airesult` ELSE NULL END) AS 'VIairesult' ,       MAX(  CASE WHEN `type`=2 THEN `airesult` ELSE NULL END) AS 'VBairesult' ,        MAX( CASE WHEN `type`=0 THEN `manualresult` ELSE NULL END) AS 'ELRengongResult' ,       MAX( CASE WHEN `type`=1 THEN `manualresult` ELSE NULL END) AS 'VIRengongResult' ,        MAX( CASE WHEN `type`=2 THEN `manualresult` ELSE NULL END) AS 'VBRengongResult' ,       MAX(`line_id`)AS line_id,     MAX(`equipment_id`)AS equipment_id, MAX(`ip`)AS ip,   MAX(`shift_start_time`)AS shift_start_time,MAX(`shiftings`)AS shiftings,           MAX(`currentshift`) AS currentshift,MAX(`classify`) AS classify       FROM ng  LEFT JOIN site ON ng.`site_id`=site.id LEFT JOIN info ON ng.`id`=info.`ngId`         ";

        public const string ColRowSql2 = " GROUP BY lotid";
    }
}

/*
 * 
 SELECT 
`lotid`,
  `info`,
  `image`,
  `checked`,
  `date`,
  `type`,
  `aiinfo`,
  ng.`id` AS id,
  `site_id`,
  `finishtime`,
  `airesult`,
  `manualresult`,
  `mes_check`,
  `line_id`,
  `equipment_id`,
  `ip`,
  `row_number`,
  `row_type`,
  `row_order`,
  `column_number`,
  `column_type`,
  `column_order`,
  `top_bottom_margin`,
  `left_right_margin`,
  `shift_start_time`,
  `shiftings`,
  `currentshift`,
   `classify`,
  `px`,
  `py`,
  `width`,
  `height`,
  `ngId`,
  `creator`,
  `celllocation`

FROM ng  LEFT JOIN site ON ng.`site_id`=site.id LEFT JOIN info ON ng.`id`=info.`ngId`
 * 
 */